﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;

namespace DBManager.Admin
{
    public class NewDAL
    {
        public static DataTable getAllZone()
        {
            string sql = "select * from ZoneNews where ParentId = 0";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllZoneByParentId(string ParentId)
        {
            string sql = "select * from ZoneNews where ParentId =" + comm.to_sqlnumber(ParentId);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getInfomationZoneByID(string ZoneId)
        {
            string sql = "select * from ZoneNews where ZoneId =" + comm.to_sqlnumber(ZoneId);
            return DBUtil.SelectTable(sql);
        }
        public static string addZone(string title, string url, string ParentId, string status)
        {
            string kq = "0";
            string sql = @"INSERT INTO ZoneNews(title,url,ParentId,status)
                           VALUES 
                           (" + comm.to_sqltext(title) + @"
                           ," + comm.to_sqltext(url) + @"
                           ," + comm.to_sqlnumber(ParentId) + @"
                           ," + comm.to_sqlnumber(status) + @")";
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string editZone(string title, string url, string ParentId, string status, string zoneid)
        {
            string kq = "0";
            string sql = @"UPDATE ZoneNews
                           SET title = " + comm.to_sqltext(title) + @"
                          ,url = " + comm.to_sqltext(url) + @"
                          ,ParentId = " + comm.to_sqlnumber(ParentId) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                           WHERE zoneid=" + comm.to_sqlnumber(zoneid);
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        //news
        public static DataTable getAllNews(string sFind, string sZoneID)
        {
            string dk = "";
            string dkZone = "";
            if (sFind != "")
            {
                dk = " and title like '%" + sFind + "%' or sapo like '%" + sFind + "%' ";
            }
            if (sZoneID != "0")
            {
                dkZone = " and (zoneid=" + comm.to_sqlnumber(sZoneID) + " or ZoneIdOther like '%" + sZoneID + "," + "%' )";
            }
            string sql = "select LastModifiedDate,DistributionDate,id,case status when 0 then N'Không Dùng' when 1 then N'Dùng' end as trangthai,title,Avatar from news where 0=0 " + dk + dkZone + " order by id desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationNews(string newsID)
        {
            string sql = "select * from News where id=" + comm.to_sqlnumber(newsID);
            return DBUtil.SelectTable(sql);
        }
        public static string addNews(string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent)
        {
            //them moi tin tuc
            string kq = "0";
            try
            {
                string sql = @"INSERT INTO News
                       (
                       ZoneId
                       ,ZoneIdOther
                       ,Title
                       ,Sapo
                       ,Avatar
                       ,Author
                       ,IsFocus
                       ,DistributionDate
                       ,Url
                       ,Tags
                       ,status
                       ,LastModifiedDate
                       ,longContent)
                        VALUES
                       (
                       " + comm.to_sqlnumber(ZoneId) + @"
                       ," + comm.to_sqltext(ZoneIdOther) + @"
                       ," + comm.to_sqltext(Title) + @"
                       ," + comm.to_sqltext(Sapo) + @"
                       ," + comm.to_sqltext(Avatar) + @"
                       ," + comm.to_sqltext(Author) + @"
                       ," + comm.to_sqlnumber(IsFocus) + @"
                       ,getdate()
                       ," + comm.to_sqltext(Url) + @"
                       ," + comm.to_sqltext(Tags) + @"
                       ," + comm.to_sqltext(status) + @"
                       ,getdate()
                       ," + comm.to_sqltext(longContent) + ")";
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string EditNews(string ID, string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent)
        {
            //them moi tin tuc
            string kq = "0";
            try
            {
                string sql = @"UPDATE News
                           SET
                           ZoneId = " + comm.to_sqlnumber(ZoneId) + @"
                          ,ZoneIdOther = " + comm.to_sqltext(ZoneIdOther) + @"
                          ,Title = " + comm.to_sqltext(Title) + @"
                          ,Sapo = " + comm.to_sqltext(Sapo) + @"
                          ,Avatar = " + comm.to_sqltext(Avatar) + @"
                          ,Author = " + comm.to_sqltext(Author) + @"
                          ,IsFocus = " + comm.to_sqlnumber(IsFocus) + @"
                          ,Url = " + comm.to_sqltext(Url) + @"
                          ,Tags = " + comm.to_sqltext(Tags) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                          ,LastModifiedDate = getdate()
                          ,longContent = " + comm.to_sqltext(longContent) + @"
                          WHERE id=" + comm.to_sqlnumber(ID);
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
    }
}
